package control;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import db.DBConnect;
import model.Resources;
import util.Pagination;




public class ResourcesControl {
	
    public static Pagination pageInstance = null;
	//逐条添加资源信息
	public boolean insertResources(Resources resources){
		DBConnect dbc = new DBConnect();
		String sql = "insert into resources (name,type,downloadtimes,uploadtime,path,introduction,uploaduserId,isRecommend) values (?,?,?,?,?,?,?,?)";
		dbc.prepareStatement(sql);
		try{
			dbc.setString(1, resources.getName());
			dbc.setString(2, resources.getType());
			dbc.setInt(3, resources.getDownloadtimes());
			dbc.setString(4, resources.getUploadtime());
			dbc.setString(5, resources.getPath());
			dbc.setString(6, resources.getIntroduction());
			dbc.setInt(7, resources.getUploaduserId());
			dbc.setInt(8, resources.getIsRecommend());
			dbc.executeUpdate();
			return true;
		}catch(SQLException e){
			e.printStackTrace();
			System.out.println("异常产生！");
			return false;
		}finally{
			dbc.close();
		}
	}
	//批量上传资源
	public boolean insertResources_many(Resources resources){
		DBConnect dbc = new DBConnect();
		String sql = "insert into resources (name,path) values (?,?)";
		dbc.prepareStatement(sql);
		try{
			dbc.setString(1, resources.getName());
			dbc.setString(2, resources.getPath());
			dbc.executeUpdate();
			return true;
		}catch(SQLException e){
			e.printStackTrace();
			System.out.println("异常产生！");
			return false;
		}finally{
			dbc.close();
		}
	}
	public boolean updateResources(Resources resources,int id){
		DBConnect dbc = new DBConnect();
		
		String sql = "UPDATE resources SET name=?,type=?,path=?,introduction=?,isRecommend=? WHERE id=?";
		dbc.prepareStatement(sql);
		try {
			dbc.setString(1,resources.getName());
			dbc.setString(2, resources.getType());
			dbc.setString(3, resources.getPath());
			dbc.setString(4, resources.getIntroduction());
			dbc.setInt(5,resources.getIsRecommend());
			dbc.setInt(6, id);
			dbc.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("异常产生!");
			return false;
		} finally {
			dbc.close();
		}	
	}
	
	public boolean updateResourcesDownloadtimes(Resources resources,int id){
		DBConnect dbc = new DBConnect();
		
		String sql = "UPDATE resources SET downloadtimes=? WHERE id=?";
		dbc.prepareStatement(sql);
		try {
			dbc.setInt(1,resources.getDownloadtimes());
			dbc.setInt(2, id);
			dbc.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("异常产生!");
			return false;
		} finally {
			dbc.close();
		}
	}
	
	public boolean deletResources(int id){
		DBConnect dbc = new DBConnect();
		String sql = "DELETE FROM resources where id = ? ";
		dbc.prepareStatement(sql);
		try{
			dbc.setInt(1, id);
			dbc.executeUpdate();
			return true ;
		}catch(SQLException e){
			e.printStackTrace();
			System.out.println("异常产生！");
			return false;
		}finally{
			dbc.close();
		}
	}
	
	
	public Resources getResourcesInformation(int id){
		DBConnect dbc = new DBConnect();
		String sql = "SELECT * FROM resources WHERE id = ?";
		dbc.prepareStatement(sql);
		try {
			Resources resources = new Resources();
			dbc.setInt(1, id);
			ResultSet rs = dbc.executeQuery();
			if(rs.next())
			{
				resources.setId(rs.getInt("id"));
				resources.setName(rs.getString("name"));
				resources.setType(rs.getString("type"));
				resources.setDownloadtimes(rs.getInt("downloadtimes"));
				resources.setUploadtime(rs.getString("uploadtime"));
				resources.setPath(rs.getString("path"));
				resources.setIntroduction(rs.getString("introduction"));
				resources.setUploaduserId(rs.getInt("uploaduserId"));
				resources.setIsRecommend(rs.getInt("isRecommend"));

				return resources;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			dbc.close();
		}
		return null;
	}
	
	public boolean deleteResources(int id){
		DBConnect dbc = new DBConnect();
		String sql = "DELETE FROM resources WHERE id=?";
		dbc.prepareStatement(sql);
		try {
			dbc.setInt(1, id);
			dbc.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			dbc.close();
		}
	}
	
	//默认按时间排序
	public Collection getResources(int page,int rowsPerPage)throws SQLException, Exception
	{
		String sql="SELECT * FROM resources order by uploadtime desc";
		pageInstance = new ResourcesControlPage(sql, rowsPerPage);
		try {
			return pageInstance.getPage(page);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	//名字查找信息
	public Collection getResources(int page,int rowsPerPage,String name)throws SQLException, Exception
	{
		String sql = "SELECT * FROM resources where name = '"+name+"' order by uploadtime desc";
		pageInstance = new ResourcesControlPage(sql, rowsPerPage);
		try {
			return pageInstance.getPage(page);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	public Collection getResources_1(int page, int rowsPerPage,String type,String key) throws SQLException, Exception
	{
		String sql = "SELECT * FROM resources where "+type+" like '%"+key+"%' order by uploadtime desc";
		pageInstance = new ResourcesControlPage(sql, rowsPerPage);
		try {
			return pageInstance.getPage(page);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	public Collection getResources_2(int page, int rowsPerPage,String sort_field_1,String sort_field_2) throws SQLException, Exception
	{
		String sql = "SELECT * FROM resources order by "+sort_field_1 +" desc,"+sort_field_2+"";
		pageInstance = new ResourcesControlPage(sql, rowsPerPage);
		try {
			return pageInstance.getPage(page);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
}

class ResourcesControlPage extends Pagination {
	public ResourcesControlPage(String sql, int rowsPerPage) {
		super(sql, rowsPerPage, 1);
	}
	@Override
	protected Collection<Resources> packResultSet(ResultSet rs) throws SQLException {
		Collection<Resources>resourcesList = new ArrayList<Resources>();
		Resources resources = null;
		while (rs.next()) {
			resources=new Resources();
			resources.setId(rs.getInt("id"));
			resources.setName(rs.getString("name"));
			resources.setType(rs.getString("type"));
			resources.setDownloadtimes(rs.getInt("downloadtimes"));
			resources.setUploadtime(rs.getString("uploadtime"));
			resources.setPath(rs.getString("path"));
			resources.setIntroduction(rs.getString("introduction"));
			resources.setUploaduserId(rs.getInt("uploaduserId"));
			resources.setIsRecommend(rs.getInt("isRecommend"));
			resourcesList.add(resources);
			resources=null;
		}
		return resourcesList;
	}
}
	
